﻿using QWManage.Model;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace QWManage.DAL
{
    public class UserDAL : SqlDAL
    {
        public UserDAL()
        {
            TableName = "QW_Users";
        }
        public List<string> GetUserIdsRequired()
        {
            List<string> list = new List<string>();
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select UserID from {TableName} where Required=1";
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string userId = reader.GetString(0);
                        list.Add(userId);
                    }
                }
                return list;
            }
            finally
            {
                conn.Close();
            }
        }
        public List<UserInfo> GetUserInfos()
        {
            List<UserInfo> list = new List<UserInfo>();
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select * from {TableName} order by [Name]";
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        UserInfo userInfo = GetUserInfo(reader);
                        list.Add(userInfo);
                    }
                }
                return list;
            }
            finally
            {
                conn.Close();
            }
        }

        private bool Insert(UserInfo obj)
        {
            string sql = $"insert into {TableName} " +
                $"(UserID,Name,Alias) values " +
                $"(@UserID,@Name,@Alias) ";
            using (SqlConnection conn = new SqlConnection(SQLCON))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@UserID", obj.UserID));
                cmd.Parameters.Add(GetStringSqlParameter("@Name", obj.Name));
                cmd.Parameters.Add(GetStringSqlParameter("@Alias", obj.Alias));
                conn.Open();
                return cmd.ExecuteNonQuery() == 1;
            }
        }
        public bool UpdateRequired(string userId, int required)
        {
            string sql = $"Update {TableName} set " +
                   $"Required=@Required " +
                   $"where UserID=@UserID";
            using (SqlConnection conn = new SqlConnection(SQLCON))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(GetIntSqlParameter("@Required", required));
                cmd.Parameters.Add(GetStringSqlParameter("@UserID", userId));
                return cmd.ExecuteNonQuery() == 1;
            }
        }
        public bool UpdateYiYeUserId(string userId, string yiyeUserId)
        {
            string sql = $"Update {TableName} set " +
                   $"YiYeUserId=@YiYeUserId " +
                   $"where UserID=@UserID";
            using (SqlConnection conn = new SqlConnection(SQLCON))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(GetStringSqlParameter("@yiYeUserId", yiyeUserId));
                cmd.Parameters.Add(GetStringSqlParameter("@UserID", userId));
                return cmd.ExecuteNonQuery() == 1;
            }
        }

        private bool Update(UserInfo obj)
        {
            string sql = $"Update {TableName} set " +
                $"Name=@Name," +
                $"Alias=@Alias, " +
                $"Required=@Required, " +
                $"YiYeUserId=@YiYeUserId " +
                $"where UserID=@UserID";
            using (SqlConnection conn = new SqlConnection(SQLCON))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(GetStringSqlParameter("@Name", obj.Name));
                cmd.Parameters.Add(GetStringSqlParameter("@Alias", obj.Alias));
                cmd.Parameters.Add(GetIntSqlParameter("@Required", obj.Required));
                cmd.Parameters.Add(GetStringSqlParameter("@YiYeUserId", obj.YiYeUserId));
                cmd.Parameters.Add(new SqlParameter("@UserID", obj.UserID));
                return cmd.ExecuteNonQuery() == 1;
            }
        }

        public bool Add(UserInfo obj)
        {
            if (GetUserInfo(obj.UserID) == null)
                return Insert(obj);
            else
                return Update(obj);
        }
        public UserInfo GetUserInfoByYiYeUserId(string yiYeUserId)
        {
            UserInfo obj = null;
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select * from {TableName} where YiYeUserId=@YiYeUserId";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@YiYeUserId", yiYeUserId));
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        obj = GetUserInfo(reader);
                    }
                }
                return obj;
            }
            finally
            {
                conn.Close();
            }
        }
        public UserInfo GetUserInfo(string userid)
        {
            UserInfo obj = null;
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select * from {TableName} where UserID=@userid";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@UserID", userid));
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        obj = GetUserInfo(reader);
                    }
                }
                return obj;
            }
            finally
            {
                conn.Close();
            }
        }

        private UserInfo GetUserInfo(SqlDataReader reader)
        {
            UserInfo obj = new UserInfo();
            obj.UserID = ReadString(reader, "UserID");
            obj.Name = ReadString(reader, "Name");
            obj.Alias = ReadString(reader, "Alias");
            obj.Required = ReadIntNull(reader, "Required");
            obj.YiYeUserId = ReadString(reader, "YiYeUserId");
            return obj;
        }
    }
}
